'''

将sql转化成excel

'''

from db.mysql import MysqlClient
import csv, os, time

# 实例化数据库
mysql = MysqlClient()

# 上周
refer = {

    "泰康保险": ["泰康康护一生重大疾病保险产品计划", "泰无忧百万防癌保"],
    "中华保险": ["中华一心一意重大疾病保险计划"],
    "众安保险": ["乐活e生2019重大疾病保险", "女性专属疾病保险", "尊享e生爸妈版"],
    "华夏保险": ["华夏嘟佩奇定期寿险", "华夏常青藤防癌医疗保险"],
    "华安保险": ["顺意e生百万医疗保险"],
    "和谐健康保险": ["和谐健康宝宝少儿定期重大疾病保险计划"],
    "大都会人寿": ["都会天使医疗保险"],
    "太平洋保险": ["e宝无忧少儿重疾险", "太健康·百万全家桶", "悦·馨保女性特定疾病保险", "悦享保个人癌症医疗保障计划"],
    "安盛保险": ["千万综合交通意外险"],
    "昆仑健康保险": ["康爱保疾病保障计划"],
    "横琴人寿": ["尊享人生医疗保险（2018版）"],
    "阳光保险集团": ["阳光个人意外伤害保险"]

}
# 本周
refer_20191227 = {

    "大都会人寿": [{"name": "都会二号疾病保险产品组合-5年缴", "type": "重疾险"}, {"name": "都会二号疾病保险产品组合-10年缴", "type": "重疾险"},
              {"name": "都会二号疾病保险产品组合-20年缴", "type": "重疾险"},{"name":"都会一号健康保险产品组合","type":"医疗险"}],
    "都邦保险": [{"name": "都健康  全民百万医疗", "type": "医疗险"}],
    "太平洋保险": [{"name": "个人意外综合保险", "type": "意外险"}, {"name": "全民运动意外保险产品计划", "type": "意外险"},
              {"name": "燃动力”运动意外伤害保险", "type": "意外险"}, {"name": "无微宝意外险", "type": "意外险"},
              {"name": "悦宝保·学幼住院医疗保险", "type": "医疗险"}],

}

refer_20200110 = {
    "平安保险":[
        {"name":"平安e生保","type":"医疗险"},
        {"name":"平安e生保2017版","type":"医疗险"},
        {"name":"少儿健康医疗保险","type":"医疗险"},
        {"name":"幸孕星19母婴医疗险","type":"医疗险"}
    ],
    "中国人民保险":[
        {"name":"人人安康家庭医疗保险","type":"医疗险"}
    ],
    "泰康保险":[
        {"name":"泰爱保·百万医疗险（2019版）","type":"医疗险"},
        {"name":"泰无忧.重疾医疗险","type":"医疗险"},
        {"name":"药神保·抗癌特药保障计划（基础款）","type":"医疗险"}
    ],
    "昆仑保险":[
        {"name":"昆仑健康保重大疾病保险（2.0版）","type":"重疾险"}
    ],
    "安盛保险":[
        {"name":"盛放人生——全球癌症医疗保险","type":"医疗险"},
        {"name":"卓越守护百万住院医疗保险（高血压患者专款）","type":"医疗险"}
    ],
    "众安保险":[
        {"name":"孝欣保恶性肿瘤医疗险特惠版","type":"医疗险"}
    ],

}
refer_20200113={
    "中华保险":[
        {"name":"中华怡康终身重大疾病保险","type":"重疾险"}
    ],
    "中国大地保险":[
        {"name":"中老年防癌医疗险","type":"医疗险"}
    ],
    "平安保险":[
        {"name":"成人全面重疾保险","type":"重疾险"},
        {"name":"福运安康重疾保险产品计划","type":"重疾险"}
    ],
    "中国太平":[
        {"name":"康爱卫士老年恶性肿瘤疾病保险","type":"重疾险"},
        {"name":"一生无忧恶性肿瘤医疗险","type":"医疗险"},
        {"name":"医保无忧百万医疗险（2019尊享版）","type":"医疗险"}
    ],
    "昆仑健康保险":[
        {"name":"惠多保重大疾病保险","type":"重疾险"},
    ],
    "同方全球人寿":[
        {"name":"同佑e生重大疾病保险产品计划","type":"重疾险"},

    ],
    "华安保险":[
        {"name":"药神保·抗癌特药保障计划（基础款）","type":"医疗险"}
    ],
    "德华安顾人寿":[
        {"name":"孝亲宝","type":"重疾险"}
    ],
    "长生人寿保险":[
        {"name":"长生爱康保重大疾病保险","type":"重疾险"}
    ]
}
refer_20200121 = {
    "阳光保险集团":[
        {"name":"健康随e保重疾保障计划（成年人版）","type":"重疾险"},
        {"name":"健康随e保重疾保障计划（儿童版）","type":"重疾险"}
    ],
    "平安保险":[
        {"name":"平安成人重大疾病保险","type":"重疾险"},
        {"name":"抗癌卫士2018","type":"重疾险"}
    ],
    "中国人寿保险":[
        {"name":"如E贝贝（尊享版）少儿保险产品计划","type":"重疾险"},

    ],
    "信泰保险":[
        {"name":"信泰i立方恶性肿瘤疾病保险","type":"重疾险"}
    ],
    "中国太平":[
        {"name":"太平关爱今生重疾保障计划","type":"重疾险"}
    ]

}
refer_20200207 = {
    "京东安联":[
        {"name":"京彩一生防癌医疗保险","type":"重疾险"}
    ],
    "中国太平":[
        {"name":"上海医保账户重大疾病保险","type":"重疾险"}
    ],
    "大都会人寿":[
        {"name":"天下无疾重大疾病保险","type":"重疾险"}

    ],
    "华安保险":[
        {"name":"华安顺意人生百种重疾保险","type":"重疾险"}
    ],
    "华夏保险":[
        {"name":"华夏福重大疾病保障计划","type":"重疾险"}
    ],
    "横琴人寿":[
        {"name":"横琴优康保终身重大疾病保险","type":"重疾险"}
    ],
    "昆仑健康保险":[
        {"name":"健康保重大疾病保险(至尊版)保险产品计划","type":"重疾险"}
    ],
    "合众人寿":[
        {"name":"合众新爱无忧特定恶性肿瘤疾病保险","type":"重疾险"}
    ],
    "泰康人寿":[
        {"name":"泰康全能保（2017）保险产品计划","type":"重疾险"}
    ],
    "阳光保险集团":[
        {"name":"i保终身重大疾病保险2019版","type":"重疾险"}
    ],
    "海保人寿":[
        {"name":"海保人寿大金刚A款重大疾病保险","type":"重疾险"}
    ]

}
refer_20200214 = {
    "安华农业保险":[
        {"name":"安农之安行天下","type":"意外险"},
    ],
    "永诚保险":[
        {"name":"公共交通工具乘客意外险","type":"意外险"}
    ],
    "横琴人寿":[
        {"name":"海陆空交通意外险","type":"意外险"}
    ],
    "锦泰保险":[
        {"name":"公共交通意外保障（一年期）","type":"意外险"},
        {"name":"航空意外险","type":"意外险"}
    ],
    "华夏保险":[
        {"name":"空中一号航空意外伤害保险","type":"意外险"}
    ],
    "中国人寿":[
        {"name":"如E航空保","type":"意外险"}
    ],
    "阳光报下集团":[
        {"name":"阳光出行保交通工具意外险","type":"意外险"}
    ],
    "华安保险":[
        {"name":"一路平安交通工具意外伤害保险","type":"意外险"}
    ],
    "大都会人寿":[
        {"name":"陪伴终身重疾保险产品组合","type":"重疾险"}
    ],
    "太平保险":[
        {"name":"太平退糖鼓糖尿病并发症保险","type":"重疾险"}
    ],
    "泰康人寿":[
        {"name":"e无忧泰康重疾保","type":"重疾险"},
        {"name":"泰康健康保保险产品计划","type":"重疾险"}
    ],


}
refer_20200221 = {
    "中国人民保险":[
        {"name":"金色重阳防癌疾病保险","type":"重疾险"},
        {"name":"营运交通工具乘客意外险","type":"意外险"},


    ],
    "中国太平":[
        {"name":"太平金生康瑞终身重大疾病保险","type":"重疾险"}
    ],
    "瑞华保险":[
        {"name":"康瑞保重大疾病保险","type":"重疾险"},
        {"name":"瑞华爱康保多次给付重大疾病保险","type":"重疾险"},
        {"name":"瑞华爱宝保少儿重大疾病保险","type":"重疾险"},
    ],


}
refer_20200228 = {
    "泰康保险":[
        {"name":"e家保家庭共享健康保障计划","type":"重疾险"}
    ],
    "华夏保险":[
        {"name":"华夏医保通（普惠版）医疗保险计划","type":"医疗险"},
        {"name":"华夏常春藤重大疾病保障计划","type":"重疾险"},
        {"name":"华夏常青树重大疾病保险","type":"重疾险"},
    ],
    "平安保险":[
        {"name":"平安i无忧医疗保险","type":"医疗险"}
    ],
    "国华人寿":[
        {"name":"国华少儿成长无忧重大疾病保险","type":"重疾险"}
    ]
}
# 执行程序
def ExecResult():
    #创建表头信息
    writeExcel( )

    for k in refer_20200228:

        for v in refer_20200228[k]:
            # 意外险直接读取数据库
            if v["type"] == "意外险":

                mysql.cursor.execute('SELECT * FROM accident WHERE name = "'+v["name"]+'"')

            else:

                mysql.cursor.execute('SELECT * FROM `{0}` WHERE state = 2'.format( v["name"]))

            result = mysql.cursor.fetchall()

            prices = [ float( v["price"] ) for v in result ]

            MAX_PRICE = max( prices )

            MIN_PRICE = min( prices )

            price = '%s~%s'%( str(MIN_PRICE),str(MAX_PRICE))

            data = [[v["name"],v["type"],price,k]]

            writeExcel(data)

            print("产品信息写入成功:%s"%v["name"])



# 创建文件
def createFile(filename):
    if not os.path.exists(filename):
        os.mkdir(filename)

# 写入Excel
def writeExcel(content=None,date=time.strftime('%Y%m%d',time.localtime(time.time()))):

    # 如果content = None 创建表头
    if content == None:

        with open('./{0}.csv'.format(date), mode='a+', newline='') as csv_file:
            # 定义表头
            header = [[" 产品名称", "保险类型", "价格区间(min~max)","所属公司"]]

            writer = csv.writer(csv_file)
            # 创建表头
            writer.writerows(header)

        return

    # 写入内容
    else:

        with open('./{0}.csv'.format(date,content), mode='a+', newline='') as csv_file:

            writer = csv.writer(csv_file)
            # 创建表头
            writer.writerows(content)

            return


if __name__ == '__main__':
    ExecResult()
